const { query: dbQuery, getConnection } = require('../config/database');
const { logger } = require('../utils/logger');

/**
 * 订单模型类
 * 处理订单相关的数据库操作
 */
class Order {
  constructor(data = {}) {
    this.id = data.id;
    this.order_number = data.order_number;
    this.user_id = data.user_id;
    this.status = data.status || 'pending';
    this.order_type = data.order_type || 'dine_in'; // dine_in, takeaway, reservation_dine_in, reservation_takeaway
    this.table_number = data.table_number;
    this.delivery_address = data.delivery_address;
    this.delivery_phone = data.delivery_phone;
    this.delivery_notes = data.delivery_notes;
    this.subtotal = data.subtotal || 0;
    this.tax_amount = data.tax_amount || 0;
    this.discount_amount = data.discount_amount || 0;
    this.delivery_fee = data.delivery_fee || 0;
    this.total_amount = data.total_amount || 0;
    this.payment_method = data.payment_method;
    this.payment_status = data.payment_status || 'pending';
    this.payment_id = data.payment_id;
    this.coupon_id = data.coupon_id;
    this.notes = data.notes;
    this.estimated_time = data.estimated_time;
    this.completed_at = data.completed_at;
    this.cancelled_at = data.cancelled_at;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
  }

  /**
   * 生成订单号
   * 新的编号规则：
   * 前缀字母规则：
   * - N = Now（即时订单）
   * - R = Reservation（预订订单）
   * - D = Dine-in（堂食）
   * - T = Take-away（外带）
   * - G = Guest（非会员用户，可选前缀）
   * 
   * 组合示例：
   * - ND = Now + Dine-in（会员即时堂食单）
   * - RT = Reservation + Take-away（会员预订外带单）
   * - GND = Guest + Now + Dine-in（非会员即时堂食单）
   * - GRT = Guest + Reservation + Take-away（非会员预订外带单）
   * 
   * 编号部分：
   * - 5168 为起始编号
   * - 每新增一单，数字部分依次递增 +1
   */
  static async generateOrderNumber(orderType = 'dine_in', isGuest = true) {
    // 计算前缀
    const computePrefix = () => {
      // 判断是否为预订订单
      const isReservation = String(orderType || '').startsWith('reservation');
      
      // 判断订单类型
      const isDineIn = String(orderType || '').includes('dine_in') || orderType === 'dine_in';
      const isTakeaway = String(orderType || '').includes('takeaway') || orderType === 'takeaway';
      
      // 构建前缀
      const guestPrefix = isGuest ? 'G' : '';  // 非会员用户添加G前缀
      const timePrefix = isReservation ? 'R' : 'N';  // 预订R，即时N
      const typePrefix = isTakeaway ? 'T' : 'D';  // 外带T，堂食D（默认）
      
      return guestPrefix + timePrefix + typePrefix;
    };

    const prefix = computePrefix();

    // 从数据库查询当前符合新格式的最大编号
    let maxNum = null;
    try {
      const rows = await dbQuery(
        "SELECT MAX(CAST(SUBSTRING_INDEX(order_number, '-', -1) AS UNSIGNED)) AS max_num FROM orders WHERE order_number REGEXP '^[GNR]*[NR][DT]-[0-9]+$'"
      );
      maxNum = rows && rows[0] && rows[0].max_num ? Number(rows[0].max_num) : null;
    } catch (e) {
      // 兼容 mock DB：退化为读取所有订单号后在内存中计算最大尾号
      try {
        const rows2 = await dbQuery('SELECT order_number FROM orders');
        if (Array.isArray(rows2)) {
          for (const r of rows2) {
            const on = r.order_number || r.orderNumber;
            if (typeof on === 'string') {
              // 匹配新格式：[G可选][N/R][D/T]-数字
              const m = on.match(/^[GNR]*[NR][DT]-(\d+)$/);
              if (m) {
                const n = Number(m[1]);
                if (!Number.isNaN(n)) {
                  maxNum = maxNum == null ? n : Math.max(maxNum, n);
                }
              }
            }
          }
        }
      } catch (_) {
        // 忽略，保持 maxNum 为 null
      }
    }

    const base = 5168;
    const nextNum = maxNum != null && maxNum >= base ? (maxNum + 1) : base;

    return `${prefix}-${nextNum}`;
  }

  /**
   * 创建新订单
   */
  static async create(orderData, orderItems) {
    const connection = await getConnection();
    
    try {
      await connection.beginTransaction();

      const {
        user_id, order_type, table_number, delivery_address,
        delivery_phone, delivery_notes, subtotal, tax_amount,
        discount_amount, delivery_fee, total_amount, payment_method,
        coupon_id, notes, estimated_time
      } = orderData;

      // 使用客户端提供的订单号，如果没有则生成新的
      let orderNumber = orderData.order_number;
      console.log('DEBUG: orderData.order_number =', orderData.order_number);
      console.log('DEBUG: user_id =', user_id, 'order_type =', order_type);
      if (!orderNumber) {
        // 判断是否为游客（user_id为null或undefined表示游客）
        const isGuest = !user_id;
        console.log('DEBUG: isGuest =', isGuest);
        orderNumber = await Order.generateOrderNumber(order_type, isGuest);
        console.log('DEBUG: generated orderNumber =', orderNumber);
      } else {
        console.log('DEBUG: using provided orderNumber =', orderNumber);
      }

      // 创建订单
      const [orderResult] = await connection.execute(
        `INSERT INTO orders (
          order_number, user_id, status, order_type, table_number,
          delivery_address, delivery_phone, delivery_notes,
          subtotal, tax_amount, discount_amount, delivery_fee,
          total_amount, payment_method, coupon_id, notes, estimated_time
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          orderNumber, user_id, 'pending', order_type, table_number,
          delivery_address, delivery_phone, delivery_notes,
          subtotal, tax_amount, discount_amount, delivery_fee,
          total_amount, payment_method, coupon_id, notes, estimated_time
        ]
      );
      console.log('DEBUG: INSERT result =', orderResult);
      console.log('DEBUG: insertId =', orderResult.insertId);

      const orderId = orderResult.insertId;

      // 创建订单项
      if (orderItems && orderItems.length > 0) {
        const itemValues = orderItems.map(item => [
          orderId, item.menu_item_id, item.quantity,
          item.unit_price, item.total_price, item.notes
        ]);

        await connection.execute(
          `INSERT INTO order_items (
            order_id, menu_item_id, quantity, unit_price, total_price, notes
          ) VALUES ${itemValues.map(() => '(?, ?, ?, ?, ?, ?)').join(', ')}`,
          itemValues.flat()
        );
      }

      // 如果使用了优惠券，更新优惠券状态
      if (coupon_id) {
        await connection.execute(
          'UPDATE user_coupons SET is_used = 1, used_at = NOW() WHERE id = ? AND user_id = ?',
          [coupon_id, user_id]
        );
      }

      await connection.commit();
      console.log('Order.create: About to call findById with orderId:', orderId);
      const foundOrder = await Order.findById(orderId);
      console.log('Order.create: findById returned:', foundOrder);
      return foundOrder;
    } catch (error) {
      await connection.rollback();
      logger.error('创建订单失败:', error);
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 根据ID查找订单
   */
  static async findById(id) {
    try {
      console.log('Order.findById called with id:', id);
      const rows = await dbQuery(
        'SELECT * FROM orders WHERE id = ?',
        [id]
      );
      console.log('Order.findById query result:', rows);

      if (rows.length === 0) {
        console.log('Order.findById: No order found with id:', id);
        return null;
      }

      console.log('Order.findById: Found order:', rows[0]);
      return new Order(rows[0]);
    } catch (error) {
      logger.error('查找订单失败:', error);
      throw error;
    }
  }

  /**
   * 根据订单号查找订单
   */
  static async findByOrderNumber(orderNumber) {
    try {
      const rows = await dbQuery(
        'SELECT * FROM orders WHERE order_number = ?',
        [orderNumber]
      );

      if (rows.length === 0) {
        return null;
      }

      return new Order(rows[0]);
    } catch (error) {
      logger.error('根据订单号查找订单失败:', error);
      throw error;
    }
  }

  /**
   * 获取用户订单列表
   */
  static async findByUserId(userId, options = {}) {
    try {
      const {
        status,
        order_type,
        page = 1,
        limit = 20,
        sort_by = 'created_at',
        sort_order = 'DESC'
      } = options;

      let query = 'SELECT * FROM orders WHERE user_id = ?';
      const params = [userId];

      if (status) {
        query += ' AND status = ?';
        params.push(status);
      }

      if (order_type) {
        query += ' AND order_type = ?';
        params.push(order_type);
      }

      // 添加排序
      const validSortFields = ['id', 'created_at', 'updated_at', 'total_amount'];
      const sortField = validSortFields.includes(sort_by) ? sort_by : 'created_at';
      const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
      query += ` ORDER BY ${sortField} ${sortDirection}`;

      // 添加分页
      const offset = (page - 1) * limit;
      query += ' LIMIT ? OFFSET ?';
      params.push(limit, offset);

      const rows = await dbQuery(query, params);
      return rows.map(row => new Order(row));
    } catch (error) {
      logger.error('获取用户订单列表失败:', error);
      throw error;
    }
  }

  /**
   * 获取所有订单（管理员用）
   */
  static async findAll(options = {}) {
    try {
      const {
        status,
        order_type,
        user_id,
        start_date,
        end_date,
        page = 1,
        limit = 20,
        sort_by = 'created_at',
        sort_order = 'DESC'
      } = options;

      let query = 'SELECT * FROM orders WHERE 1=1';
      const params = [];

      if (status) {
        query += ' AND status = ?';
        params.push(status);
      }

      if (order_type) {
        query += ' AND order_type = ?';
        params.push(order_type);
      }

      if (user_id) {
        query += ' AND user_id = ?';
        params.push(user_id);
      }

      if (start_date) {
        query += ' AND created_at >= ?';
        params.push(start_date);
      }

      if (end_date) {
        query += ' AND created_at <= ?';
        params.push(end_date);
      }

      // 添加排序
      const validSortFields = ['id', 'created_at', 'updated_at', 'total_amount', 'order_number'];
      const sortField = validSortFields.includes(sort_by) ? sort_by : 'created_at';
      const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
      query += ` ORDER BY ${sortField} ${sortDirection}`;

      // 添加分页
      const offset = (page - 1) * limit;
      query += ' LIMIT ? OFFSET ?';
      params.push(limit, offset);

      const rows = await dbQuery(query, params);
      return rows.map(row => new Order(row));
    } catch (error) {
      logger.error('获取所有订单失败:', error);
      throw error;
    }
  }

  /**
   * 获取订单详情（包含订单项）
   */
  static async getOrderWithItems(orderId) {
    try {
      const order = await Order.findById(orderId);
      if (!order) {
        return null;
      }

      // 获取订单项
      const items = await dbQuery(`
        SELECT 
          oi.*,
          mi.name as item_name,
          mi.name_en as item_name_en,
          mi.image_url as item_image
        FROM order_items oi
        LEFT JOIN menu_items mi ON oi.menu_item_id = mi.id
        WHERE oi.order_id = ?
        ORDER BY oi.id
      `, [orderId]);

      // 将Order实例转换为普通对象
      const orderData = {
        id: order.id,
        order_number: order.order_number,
        user_id: order.user_id,
        status: order.status,
        order_type: order.order_type,
        table_number: order.table_number,
        delivery_address: order.delivery_address,
        delivery_phone: order.delivery_phone,
        delivery_notes: order.delivery_notes,
        subtotal: order.subtotal,
        tax_amount: order.tax_amount,
        discount_amount: order.discount_amount,
        delivery_fee: order.delivery_fee,
        total_amount: order.total_amount,
        payment_method: order.payment_method,
        payment_status: order.payment_status,
        payment_id: order.payment_id,
        coupon_id: order.coupon_id,
        notes: order.notes,
        estimated_time: order.estimated_time,
        completed_at: order.completed_at,
        cancelled_at: order.cancelled_at,
        created_at: order.created_at,
        updated_at: order.updated_at,
        items: items
      };
      
      return orderData;
    } catch (error) {
      logger.error('获取订单详情失败:', error);
      throw error;
    }
  }

  /**
   * 更新订单状态
   */
  static async updateStatus(id, status, additionalData = {}) {
    try {
      const fields = ['status = ?', 'updated_at = NOW()'];
      const values = [status];

      // 根据状态添加时间戳
      if (status === 'completed') {
        fields.push('completed_at = NOW()');
      } else if (status === 'cancelled') {
        fields.push('cancelled_at = NOW()');
      }

      // 添加其他字段
      for (const [key, value] of Object.entries(additionalData)) {
        fields.push(`${key} = ?`);
        values.push(value);
      }

      values.push(id);

      const result = await dbQuery(
        `UPDATE orders SET ${fields.join(', ')} WHERE id = ?`,
        values
      );

      return result.affectedRows > 0;
    } catch (error) {
      logger.error('更新订单状态失败:', error);
      throw error;
    }
  }

  /**
   * 更新支付方式
   */
  static async updatePaymentMethod(id, paymentMethod) {
    try {
      const result = await dbQuery(
        'UPDATE orders SET payment_method = ?, updated_at = NOW() WHERE id = ?',
        [paymentMethod, id]
      );

      return result.affectedRows > 0;
    } catch (error) {
      logger.error('更新支付方式失败:', error);
      throw error;
    }
  }

  /**
   * 更新支付状态
   */
  static async updatePaymentStatus(id, paymentStatus, paymentData = {}) {
    try {
      const fields = ['payment_status = ?', 'updated_at = NOW()'];
      const values = [paymentStatus];

      // 添加支付相关数据
      if (paymentData.transaction_id) {
        fields.push('transaction_id = ?');
        values.push(paymentData.transaction_id);
      }

      if (paymentData.payment_time) {
        fields.push('payment_time = ?');
        values.push(paymentData.payment_time);
      }

      if (paymentData.payment_amount) {
        fields.push('payment_amount = ?');
        values.push(paymentData.payment_amount);
      }

      // 如果有旧的payment_id参数，保持兼容性
      if (paymentData.payment_id) {
        fields.push('payment_id = ?');
        values.push(paymentData.payment_id);
      }

      values.push(id);

      const result = await dbQuery(
        `UPDATE orders SET ${fields.join(', ')} WHERE id = ?`,
        values
      );

      return result.affectedRows > 0;
    } catch (error) {
      logger.error('更新支付状态失败:', error);
      throw error;
    }
  }

  /**
   * 获取订单统计
   */
  static async getStatistics(options = {}) {
    try {
      const {
        start_date,
        end_date,
        user_id
      } = options;

      let query = `
        SELECT 
          COUNT(*) as total_orders,
          COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
          COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_orders,
          COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_orders,
          SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) as total_revenue,
          AVG(CASE WHEN status = 'completed' THEN total_amount ELSE NULL END) as avg_order_value
        FROM orders
        WHERE 1=1
      `;
      const params = [];

      if (start_date) {
        query += ' AND created_at >= ?';
        params.push(start_date);
      }

      if (end_date) {
        query += ' AND created_at <= ?';
        params.push(end_date);
      }

      if (user_id) {
        query += ' AND user_id = ?';
        params.push(user_id);
      }

      const rows = await dbQuery(query, params);
      return rows[0];
    } catch (error) {
      logger.error('获取订单统计失败:', error);
      throw error;
    }
  }

  /**
   * 获取热门菜品
   */
  static async getPopularItems(options = {}) {
    try {
      const {
        start_date,
        end_date,
        limit = 10
      } = options;

      let query = `
        SELECT 
          mi.id,
          mi.name,
          mi.name_en,
          mi.image_url,
          SUM(oi.quantity) as total_quantity,
          COUNT(DISTINCT oi.order_id) as order_count,
          SUM(oi.total_price) as total_revenue
        FROM order_items oi
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        JOIN orders o ON oi.order_id = o.id
        WHERE o.status = 'completed'
      `;
      const params = [];

      if (start_date) {
        query += ' AND o.created_at >= ?';
        params.push(start_date);
      }

      if (end_date) {
        query += ' AND o.created_at <= ?';
        params.push(end_date);
      }

      query += `
        GROUP BY mi.id
        ORDER BY total_quantity DESC
        LIMIT ?
      `;
      params.push(limit);

      const rows = await dbQuery(query, params);
      return rows;
    } catch (error) {
      logger.error('获取热门菜品失败:', error);
      throw error;
    }
  }

  /**
   * 获取分类销量统计
   */
  static async getCategorySales(options = {}) {
    try {
      const { start_date, end_date, limit = 10 } = options;

      let query = `
        SELECT 
          mi.category_id AS category_id,
          SUM(oi.quantity) AS total_quantity,
          COUNT(DISTINCT oi.order_id) AS order_count,
          SUM(oi.total_price) AS total_revenue
        FROM order_items oi
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        JOIN orders o ON oi.order_id = o.id
        WHERE o.status = 'completed'
      `;
      const params = [];

      if (start_date) {
        query += ' AND o.created_at >= ?';
        params.push(start_date);
      }

      if (end_date) {
        query += ' AND o.created_at <= ?';
        params.push(end_date);
      }

      query += `
        GROUP BY mi.category_id
        ORDER BY total_revenue DESC
      `;

      if (limit) {
        query += ' LIMIT ?';
        params.push(limit);
      }

      const rows = await dbQuery(query, params);
      return rows;
    } catch (error) {
      logger.error('获取分类销量失败:', error);
      throw error;
    }
  }

  static async count(options = {}) {
    try {
      const { start_date, end_date } = options || {};
      const useMock = process.env.USE_MOCK_DB === 'true';
      const idCol = useMock ? 'o.order_id' : 'o.id';
      let sql = `SELECT COUNT(DISTINCT ${idCol}) AS total FROM orders o WHERE 1=1`;
      const params = [];
      if (start_date) {
        sql += ' AND o.created_at >= ?';
        params.push(start_date);
      }
      if (end_date) {
        sql += ' AND o.created_at <= ?';
        params.push(end_date);
      }
      const rows = await dbQuery(sql, params);
      const r = Array.isArray(rows) ? rows[0] : rows;
      return (r && (r.total ?? r.count)) || 0;
    } catch (error) {
      logger.error('订单计数失败:', error);
      return 0;
    }
  }

  static async getTopDishes(options = {}) {
    return Order.getPopularItems(options);
  }
}

module.exports = Order;